package com.siqi.hr.system.common.dao.mysql;

import com.siqi.hr.spring.SEApplicationContext;
import com.siqi.hr.system.common.dao.intf.CatchDao;
import com.siqi.hr.system.common.model.ColumnUsage;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 获取缓存数据dao接口实现类
 * @version 1.0.0, Sep 25, 2016
 * @author xiaogq
 * @since JDK1.6.0_12, Spring3.0.2
 */
@Component("catchDao")
public class CatchDaoImpl implements CatchDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<ColumnUsage> getColumnUsageList() {
        BasicDataSource bds = SEApplicationContext.getBean("dataSource");
        String url = bds.getUrl().split("\\?")[0];
        String database = url.substring(url.lastIndexOf("/") + 1);
        StringBuffer sql = new StringBuffer("SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME, kcu.REFERENCED_COLUMN_NAME, rc.UPDATE_RULE,");
        sql.append("rc.DELETE_RULE  FROM information_schema.REFERENTIAL_CONSTRAINTS as rc  LEFT JOIN information_schema.KEY_COLUMN_USAGE as kcu on ");
        sql.append(" rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME  WHERE rc.CONSTRAINT_SCHEMA = '").append(database).append("' and rc.UNIQUE_CONSTRAINT_SCHEMA = '");
        sql.append(database).append("' and rc.TABLE_NAME = kcu.TABLE_NAME and rc.REFERENCED_TABLE_NAME = kcu.REFERENCED_TABLE_NAME ");
        sql.append(" and rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and rc.DELETE_RULE = 'RESTRICT' ORDER BY kcu.REFERENCED_TABLE_NAME, kcu.TABLE_NAME");

        return this.jdbcTemplate.query(sql.toString(), new RowMapper(){
            public ColumnUsage mapRow(ResultSet rs, int rowNum) throws SQLException {
                ColumnUsage cu = new ColumnUsage();
                cu.setTableName(rs.getString("TABLE_NAME"));
                cu.setColumnName(rs.getString("COLUMN_NAME"));
                cu.setReferencedTableName(rs.getString("REFERENCED_TABLE_NAME"));
                cu.setReferencedColumnName(rs.getString("REFERENCED_COLUMN_NAME"));
                cu.setUpdateRule(rs.getString("UPDATE_RULE"));
                cu.setDeleteRule(rs.getString("DELETE_RULE"));
                return cu;
            }
        });
    }
}
